import pandas as pd
from access_manager import AccessDBManager
from database_manager import SQLiteDBManager
import config as cfg
import datetime
import numpy as np

now = datetime.datetime.now()
hour = now.hour + now.minute / 60
today=datetime.datetime.today()
fnow_time=now.strftime("%Y-%m-%d %H:%M:%S")

 # 编写SQL查询语句
if hour<8.5:#日期，班次
    today=today-datetime.timedelta(days=1)
    date1=today.strftime("%Y-%#m-%#d")
    date2=today.strftime("%Y-%m-%d")
    shift="N"
elif hour>20.5:
    date1=today.strftime("%Y-%#m-%#d")
    date2=today.strftime("%Y-%m-%d")
    shift="N"
else:
    date1=today.strftime("%Y-%#m-%#d")
    date2=today.strftime("%Y-%m-%d")
    shift="D"
print(date1,shift)
now_times=today.strftime("%Y%m%d")
# 初始化数据库管理器
db_manager = SQLiteDBManager(cfg.db_path)
if db_manager.connect():
    try:
        # 读取数据到 DataFrame
        df = pd.read_sql_query(cfg.getdb_all_sql, db_manager.conn)
        csdf=pd.DataFrame()
        csdf_dw=pd.DataFrame()
        csdf_sumbin=pd.DataFrame()
        # 处理数据，假设这里有 30 条线
        # hour=21
        # shift="D"
        for i in range(len(df)):
            # if(i==1):
            #     break
            try:
                access_path=None
                # date1="2025-5-16"
                size=df['size'][i]#获取尺寸
                id_value = df["id"][i]#获取id
                line = df["line"][i]#获取线
                ip = df["ip"][i]#获取ip
                db_path = df["db_path"][i]#获取数据库路径
                enabled = df["enabled"][i]#获取是否启用
                access_path = f"\\\{ip}{db_path}\\{date1}-{line}-{shift}.mdb"#线别数据库路径
                # print(access_path)
                sql_avgeta=""
                sql_dweta=""
                sql_sumbin=""
                if enabled==0:
                    print(f"第 {i} 行: {line}, 未启用")
                    continue
                else:
                    if size=="整片":
                        sql_avgeta=cfg.access_sql_avgeta
                        sql_dweta=cfg.access_sql_dweta
                        sql_sumbin=cfg.access_sql_sumbin
                        pass
                    elif size=="半片":
                        access_path = f"\\\{ip}{db_path}\\{date2}-{line}-{shift}.mdb"#线别数据库路径
                        sql_avgeta=cfg.tpe_sql_avgeta
                        sql_dweta=cfg.tpe_sql_dweta
                        sql_sumbin=cfg.tpe_sql_sumbin
                        pass
                    else:
                        pass
                    print(f"第 {i} 行: {line}-{access_path}, 启用")
                     # 初始化 AccessDBManager 实例
                    access_db_manager = AccessDBManager(access_path) 
                    if access_db_manager.connect():
                        try:
                            # 1.执行平均效率查询
                            etadata = pd.read_sql_query(sql_avgeta, access_db_manager.conn)
                            etadata['BinFileName'] = etadata['BinFileName'].str.split('-S7').str[0]
                            etadata['line']=line
                            etadata['shift'] = shift
                            etadata['date']=now_times
                            if len(etadata['BinFileName'])>1:
                                etadata = etadata.groupby('BinFileName').agg({
                                    'quantity': 'sum',
                                    'eta1': lambda x: (x * etadata.loc[x.index, 'quantity']).sum() / etadata.loc[x.index, 'quantity'].sum(),
                                    'uoc1': lambda x: (x * etadata.loc[x.index, 'quantity']).sum() / etadata.loc[x.index, 'quantity'].sum(),
                                    'isc1': lambda x: (x * etadata.loc[x.index, 'quantity']).sum() / etadata.loc[x.index, 'quantity'].sum(),
                                    'ff1': lambda x: (x * etadata.loc[x.index, 'quantity']).sum() / etadata.loc[x.index, 'quantity'].sum(),
                                    'rs': 'mean',
                                    'rsh': 'mean',
                                    'irev2_lt_0': 'sum',
                                    'irev2_lt_0_05': 'sum',
                                    'irev2_lt_0_2': 'sum',
                                    'irev2_lt_0_3': 'sum',
                                    'line': 'first',
                                    'shift': 'first',
                                    'date': 'first'
                                }).reset_index()
                            csdf = pd.concat([csdf, etadata], ignore_index=True)
                            # print(csdf)
                            # 2.执行档位数据查询
                            etadata_dw = pd.read_sql_query(sql_dweta,access_db_manager.conn)
                            csdf_dw = pd.concat([csdf_dw, etadata_dw], ignore_index=True)
                            # print(csdf_dw)
                            #3.执行bin数量查询
                            etadata_dq = pd.read_sql_query(sql_sumbin,access_db_manager.conn)
                            csdf_sumbin = pd.concat([csdf_sumbin, etadata_dq], ignore_index=True)
                            # print(csdf_sumbin)
                        except Exception as e:
                            print(f"执行 SQL 查询失败: {e}")
                        finally:
                            # 关闭数据库连接
                            access_db_manager.close()
            except Exception as e:
                print(f"处理第 {i} 行时发生错误: {e}")
        # 将修改后的DataFrame更新到数据库
        delete_query = f"DELETE FROM avgeta WHERE date = '{now_times}' AND shift = '{shift}'"
        update_time_query = f"UPDATE update_time SET last_updated = '{fnow_time}' WHERE modele_name = 'avgeta'"
        delete_query_dw = f"DELETE FROM GearData1 WHERE date = '{now_times}' AND shift = '{shift}'"
        update_time_query_dw = f"UPDATE update_time SET last_updated = '{fnow_time}' WHERE modele_name = 'dangwei'"
        delete_query_sumbin = f"DELETE FROM sumbin WHERE date = '{now_times}' AND shift = '{shift}'"
        # update_time_query_sumbin = f"UPDATE update_time SET last_updated = '{fnow_time}' WHERE modele_name = 'dangwei'"
        if db_manager.conn is not None:
            try:
                db_manager.conn.execute(delete_query)
                db_manager.conn.execute(update_time_query)

                db_manager.conn.execute(delete_query_dw)
                db_manager.conn.execute(update_time_query_dw)

                db_manager.conn.execute(delete_query_sumbin)
                db_manager.conn.commit()
            except Exception as e:
                print(f"提交事务时发生错误: {e}")
                print("已删除指定日期和班次的数据")
        # 1.平均效率数据处理
        csdf.to_sql('avgeta', db_manager.conn, if_exists='append', index=False)

        # 2.档位数据处理
        csdf_dw['BinFileName'] = csdf_dw['BinFileName'].str.split('-S7').str[0]
        csdf_dw['shift'] = shift
        csdf_dw['date']=now_times
        # 检查是否有 IVgrade 数据，如果没有则添加一个默认值
        # 检查 'IvGrade' 键是否存在于 csdf_dw 列中
        if 'IvGrade' not in csdf_dw.columns:
            csdf_dw['IvGrade'] = "-"  # 如果没有 'IvGrade' 列，则添加一个空列
        else:
            csdf_dw['IvGrade'] = csdf_dw['IvGrade'].fillna('-')  # 用 '-' 替换 NaN 值
        # print(csdf_dw)
        csdf_dw = csdf_dw.groupby(['BinFileName', 'Class', 'IvGrade', 'Aoi1R']).agg({
            'sum_IvGrade': 'sum',
            'eta1': lambda x: (x * csdf_dw.loc[x.index, 'sum_IvGrade']).sum() / csdf_dw.loc[x.index, 'sum_IvGrade'].sum(),
            'shift': 'first',
            'date': 'first'
        }).reset_index()
        # csdf_dw["date_shift"]=csdf_dw["date"]+"_"+csdf_dw["shift"]
        csdf_dw.to_sql('GearData1', db_manager.conn, if_exists='append', index=False)

        # 3.bin数量数据处理
        csdf_sumbin['BinFileName'] = csdf_sumbin['BinFileName'].str.split('-S7').str[0]
        csdf_sumbin['shift'] = shift
        csdf_sumbin['date']=now_times
        csdf_sumbin = csdf_sumbin.groupby(['BinFileName','binn']).agg({
           'quantity':'sum',
           'shift': 'first',
            'date': 'first'
        }).reset_index()
        csdf_sumbin.to_sql('sumbin', db_manager.conn, if_exists='append', index=False)
        if db_manager.conn is not None:
            try:
                db_manager.conn.commit()
            except Exception as e:
                print(f"提交事务时发生错误: {e}")
        print("数据已成功更新到数据库")
        print("------------------")
    except Exception as e:
        print(f"发生错误: {e}")
    finally:
        # 关闭数据库连接
        db_manager.close()
